const { json } = require('express');
var express = require('express');
const myroute = express.Router()
const mySql = require('../model/index')

//考勤审批的接口

    // 渲染
    myroute.get('/Atten_Approval_render',(req,res)=>{
        // 设置数据库
        const sql = `
        SELECT application.mun,application_type.type,admin.name,department.department_name,application.state,application.starttime,application.endtime,application.applytime FROM application 
        LEFT JOIN application_type ON application.type = application_type.id 
        LEFT JOIN admin ON application.apply_name = admin.work_id 
        LEFT JOIN postadmin ON admin.jobs = postadmin.post_id 
        LEFT JOIN department ON postadmin.post_department = department.id
        WHERE state = "待审核";
        `;
        mySql.query(sql,(err,data)=>{
            if(err){
                console.log(err);
                console.log("渲染错了");
            }else{
                 // 把搜索到的数据传输回前端
                res.send(data);
            }
        })
    })

    // 搜索
myroute.get('/Atten_Approval_query',(req,res)=>{
console.log(req.query);
    // 设置数据库
    const sql = `
        SELECT * from (
        select application.mun,application_type.type,admin.name,department.department_name,application.state,application.starttime,application.endtime,application.applytime 
        FROM application 
        LEFT JOIN application_type ON application.type = application_type.id 
        LEFT JOIN admin ON application.apply_name = admin.work_id 
        LEFT JOIN postadmin ON admin.jobs = postadmin.post_id 
        LEFT JOIN department ON postadmin.post_department = department.id
        WHERE application.mun LIKE "%${req.query.mun}%"
        AND application_type.id LIKE "%${req.query.type}%"
        AND admin.name LIKE "%${req.query.name}%"
        AND department.department_name LIKE "%${req.query.depar}%"
        AND application.starttime LIKE "%${req.query.starttime}%"
        AND application.endtime LIKE "%${req.query.endtime}%"
        ) as ssa
        where state = "待审核";
    `;
    console.log(sql);
    mySql.query(sql,(err,data)=>{
        if(err){
            console.log(err);
            console.log("查询错了");
            res.send("查询错误")
        }else{
             // 把搜索到的数据传输回前端
            console.log("查询成功");
            res.send(data);
            console.log(data);
        }
    })
})

//考勤查询的接口
    // 渲染
    myroute.get('/Atten_Approval_render2',(req,res)=>{
        // 设置数据库
        const sql = `
        SELECT application.mun,application_type.type,admin.name,department.department_name,application.state,application.starttime,application.endtime,application.applytime FROM application 
        LEFT JOIN application_type ON application.type = application_type.id 
        LEFT JOIN admin ON application.apply_name = admin.work_id 
        LEFT JOIN postadmin ON admin.jobs = postadmin.post_id 
        LEFT JOIN department ON postadmin.post_department = department.id
        WHERE state = "已审核";
        `;
        mySql.query(sql,(err,data)=>{
            if(err){
                console.log(err);
                console.log("渲染错了");
            }else{
                 // 把搜索到的数据传输回前端
                res.send(data);
            }
        })
    })

    // 搜索
    myroute.get('/Atten_Approval_query2',(req,res)=>{
        console.log(req.query);
            // 设置数据库
            const sql = `
                SELECT * from (
                select application.mun,application_type.type,admin.name,department.department_name,application.state,application.starttime,application.endtime,application.applytime 
                FROM application 
                LEFT JOIN application_type ON application.type = application_type.id 
                LEFT JOIN admin ON application.apply_name = admin.work_id 
                LEFT JOIN postadmin ON admin.jobs = postadmin.post_id 
                LEFT JOIN department ON postadmin.post_department = department.id
                WHERE application.mun LIKE "%${req.query.mun}%"
                AND application_type.id LIKE "%${req.query.type}%"
                AND admin.name LIKE "%${req.query.name}%"
                AND department.department_name LIKE "%${req.query.depar}%"
                AND application.starttime LIKE "%${req.query.starttime}%"
                AND application.endtime LIKE "%${req.query.endtime}%"
                ) as ssa
                where state = "已审核";
            `;
            console.log(sql);
            mySql.query(sql,(err,data)=>{
                if(err){
                    console.log(err);
                    console.log("查询错了");
                    res.send("查询错误")
                }else{
                     // 把搜索到的数据传输回前端
                    console.log("查询成功");
                    res.send(data);
                    console.log(data);
                }
            })
        })
        


//考勤分析的接口

    // 渲染
    myroute.get('/Atten_analysis_render',(req,res)=>{
        // 设置数据库
        const sql = `
            SELECT admin.work_id,admin.name,status_table.status,clock.id,admin.account
            FROM clock 
            LEFT JOIN admin ON clock.word_id = admin.work_id 
            LEFT JOIN status_table ON clock.state = status_table.id 
        `;
        mySql.query(sql,(err,data)=>{
            if(err){
                console.log(err);
                console.log("渲染错了");
            }else{
                 // 把搜索到的数据传输回前端
                res.send(data);
            }
        })
    })


// 考勤设置的接口
    // 渲染
    myroute.get('/Atten_set_render',(req,res)=>{
        // 设置数据库
        const sql = `
            SELECT work_time.id,work_time.colony,work_time.forenoon,work_time.afternoon,department.department_name,work_time.man_hour,work_time.state
            FROM work_time LEFT JOIN department ON work_time.department = department.id;
        `;
        mySql.query(sql,(err,data)=>{
            if(err){
                console.log(err);
                console.log("渲染错了");
            }else{
                 // 把搜索到的数据传输回前端
                res.send(data);
            }
        })
    })
    // 搜索
    myroute.get('/Atten_set_add',(req,res)=>{
        // 设置数据库
        const sql = `
            INSERT INTO work_time VALUES(NULL,"${req.query.colony}","${req.query.forenoon}","${req.query.afternoon}","${req.query.department}","${req.query.man_hour}","${req.query.state}");
        `;
        console.log(sql);
        mySql.query(sql,(err,data)=>{
            if(err){
                res.send("错误");
                // console.log(err);
            }else{
                 // 把搜索到的数据传输回前端
                res.send(data);
                console.log(data);
            }
        })
    })

    // 修改
    myroute.get('/Atten_set_alter',(req,res)=>{
        // 设置数据库
        const sql = `
            UPDATE work_time SET
            colony="${req.query.colony}",forenoon="${req.query.forenoon}",afternoon="${req.query.afternoon}",department="${req.query.department}",man_hour="${req.query.man_hour}",state="${req.query.state}"
            WHERE id="${req.query.id}";
        `;
        console.log(sql);
        mySql.query(sql,(err,data)=>{
            if(err){
                res.send("错误");
                // console.log(err);
            }else{
                 // 把搜索到的数据传输回前端
                res.send(data);
                console.log(data);
            }
        })
    })

        // 删除
        myroute.get('/Atten_set_del',(req,res)=>{
            // 设置数据库
            const sql = `
                DELETE FROM work_time WHERE id="${req.query.id}";
            `;
            console.log(sql);
            mySql.query(sql,(err,data)=>{
                if(err){
                    res.send("错误");
                    // console.log(err);
                }else{
                     // 把搜索到的数据传输回前端
                    res.send(data);
                    console.log(data);
                }
            })
        })
    








module.exports = myroute